package com.ibm.risk.irmp.common.utils;

import com.ibm.risk.irmp.common.vo.PagedResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Component
public class DaoUtils {
    @Autowired
    NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    DataSource dataSource;
    ColumnMapRowMapper colRowMapper = new ColumnMapRowMapper();
    ColumnMapRowMapper upColRowMapper = new ColumnMapRowMapper() {
        protected String getColumnKey(String columnName) {
            return columnName.toUpperCase();
        }
    };

    public <T> PagedResult<T> pagedQueryWithCount(String sql, int pageIndex, int pageSize, Map<String, Object> params, Class<T> resultClass) {
        PagedResult<T> result = new PagedResult<>();
        if (pageIndex == 0) {
            int count = 0;

            String sqlCount = "select count(*) as CNT from (" + sql + ") as atab ";

            count = jdbcTemplate.queryForObject(sqlCount, params, Integer.class); //= countResult.size() == 0 ? 0 : ((Number) countResult.get(0).get("CNT")).intValue();
            result.setTotal(count);
            if (count == 0) {
                return result;
            }
        }
        String pageSql = sql + " limit " + pageSize + " offset " + pageIndex * pageSize;
        List<T> resultList = queryBeanList(pageSql, params, resultClass);
        result.setList(resultList);
        return result;
    }

    private <T> List queryBeanList(String pageSql, Map<String, Object> params, Class<T> resultClass) {
        return jdbcTemplate.query(pageSql, params, new BeanPropertyRowMapper(resultClass));
    }

    public PagedResult<Map<String, Object>> pagedQueryWithCount(String sql, int pageIndex, int pageSize, Map<String, Object> params) {
        return pagedQueryWithCount(sql, pageIndex, pageSize, params, true);
    }

    public PagedResult<Map<String, Object>> pagedQueryWithCount(String sql, int pageIndex, int pageSize, Map<String, Object> params, final boolean upperCase) {
        int count = 0;

        String sqlCount = "select count(*) as CNT from (" + sql + ") as atab ";

        count = jdbcTemplate.queryForObject(sqlCount, params, Integer.class); //= countResult.size() == 0 ? 0 : ((Number) countResult.get(0).get("CNT")).intValue();
        PagedResult<Map<String, Object>> result = new PagedResult<>();
        if (count == 0) {
            return result;
        }

        String pageSql = sql + " limit " + pageSize + " offset " + pageIndex * pageSize;

        List<Map<String, Object>> resultList = jdbcTemplate.query(pageSql, params, upperCase ? upColRowMapper : colRowMapper);
        result.setTotal(count);
        result.setList(resultList);
        return result;

    }


//    public Map<String, String> querySingleValue(EntityManager entityManager, String sql, Map<String, Object> params) {
//
//        Query query = entityManager.createQuery(sql);
//        if (params != null) {
//            params.forEach((k, v) -> query.setParameter(k, v));
//        }
//
//
//        Map<String, String> resultList = (Map<String, String>) query
//                //.setResultTransformer(BeanListUtils.camelMapTransformer)
//                .getSingleResult();
//        return resultList;
//    }

    public List<Map<String, Object>> pagedQuery(String sql, int pageIndex, int pageSize, Map<String, Object> params) {
        String pageSql = sql + " limit " + pageSize + " offset " + pageIndex * pageSize;
        List<Map<String, Object>> resultList = jdbcTemplate.queryForList(pageSql, params);
        return resultList;
    }

    /**
     * 获取数据库表的所有字段
     *
     * @param tableName 表名
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public List<String> getTableField(String schema, String tableName) throws SQLException, IOException {
        Connection connection = dataSource.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getColumns(null, schema, tableName, null);
        ArrayList<String> columns = new ArrayList<>();
        while (resultSet.next()) {
//            数据库名
//            String db_name = resultSet.getString("TABLE_CAT");
//            表名
//            String table_name = resultSet.getString("TABLE_NAME");
//            获取字段名
            String field = resultSet.getString("COLUMN_NAME");
//            获取字段类型
//            String fieldType = resultSet.getString("TYPE_NAME");
//            String fieldLength = resultSet.getString("COLUMN_SIZE");
//            String fieldDESC = resultSet.getString("REMARKS");
//            String info = String.format("[%s->%s->%s->%s->%s->%s]", db_name, table_name, field, fieldType, fieldLength, fieldDESC);
//            System.out.println(info);
            columns.add(field);
        }
        return columns;
    }

    public List<ColumnDef> getTableFieldDefs(String schema, String tableName) throws SQLException, IOException {
        Connection connection = dataSource.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getColumns(null, schema, tableName, null);
        ArrayList<ColumnDef> columns = new ArrayList<>();
        while (resultSet.next()) {
//            数据库名
//            String db_name = resultSet.getString("TABLE_CAT");
//            表名
//            String table_name = resultSet.getString("TABLE_NAME");
//            获取字段名
            String field = resultSet.getString("COLUMN_NAME");
//            获取字段类型
            String fieldType = resultSet.getString("TYPE_NAME");
            String fieldLength = resultSet.getString("COLUMN_SIZE");
            String fieldDesc = resultSet.getString("REMARKS");
//            String info = String.format("[%s->%s->%s->%s->%s->%s]", db_name, table_name, field, fieldType, fieldLength, fieldDESC);
//            System.out.println(info);
            columns.add(new ColumnDef(field, fieldType, fieldLength, fieldDesc));
        }
        return columns;
    }

    public static record ColumnDef(String name, String type, String length, String desc) {
    }
}
